package com.yygo.webapp.controller.export;

import com.yygo.model.LoanRequest;
import com.yygo.model.TalkRecord;
import com.yygo.webapp.controller.BaseFormController;
import com.yygo.webapp.controller.loanrequest.BaseLoanRequestFormController;
import jxl.Workbook;
import jxl.biff.DisplayFormat;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.write.Number;
import org.apache.commons.io.FileUtils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.mail.internet.MimeUtility;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.security.Principal;
import java.text.SimpleDateFormat;
import java.util.*;

import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;

/**
 * Created by stella on 2015/11/8.
 */
@Controller
@RequestMapping("/export*")
public class ExportCAFiles extends BaseLoanRequestFormController {
    private Configuration configuration = null;
    private WritableCellFormat percentFormat = new WritableCellFormat( NumberFormats.PERCENT_FLOAT);
    private DateFormat customDateFormat = new DateFormat ("yyyy-MM-dd");
    public ExportCAFiles(){
        configuration = new Configuration();
        configuration.setDefaultEncoding("UTF-8");
    }

    @RequestMapping(value = "/businessForm", method = RequestMethod.GET)
    public void exportBusinessForm(@RequestParam(required = true, value = "reqId") Long reqId, HttpServletRequest request,HttpServletResponse response) throws IOException {
        LoanRequest loanRequest = loanRequestManager.loadWholeLoanRequest(reqId);

        String path =request.getServletContext().getRealPath("")
                + "/excelTemplate/业务申请单.xls";

        File targetFile = new File(getUploadFolder(loanRequest) + "/业务申请单.xls");
        try {
            Workbook template = Workbook.getWorkbook(new File(path));
            WritableWorkbook target = Workbook.createWorkbook(targetFile, template);
            WritableSheet businessForm = target.getSheet(0);

            DateFormat customDateFormat = new DateFormat ("yyyy-MM-dd");
            WritableCellFormat dateFormat = new WritableCellFormat (customDateFormat);

            // start loan date C5 Todo: need check holiday
            Calendar calendar = Calendar.getInstance();
            Date loanDate = loanRequest.getRequestDate();
            calendar.setTime(loanDate);
            calendar.set(Calendar.DATE, calendar.get(Calendar.DATE)+1);
            DateTime C5 = new DateTime(2, 4, calendar.getTime(), dateFormat);
            businessForm.addCell(C5);
            // G26, same as loan date
            businessForm.addCell(new DateTime(6, 25, calendar.getTime(), dateFormat));
            // end loan date G5
            calendar.set(Calendar.YEAR, calendar.get(Calendar.YEAR) +3);
            DateTime G5 = new DateTime(6, 4,calendar.getTime(), dateFormat);
            businessForm.addCell(G5);
            //J26, same as end loan date
            businessForm.addCell(new DateTime(9, 25, calendar.getTime(), dateFormat));

            WritableCellFormat numberFormat = new WritableCellFormat(new NumberFormat("#.##"));
            WritableCellFormat percentFormat = new WritableCellFormat( NumberFormats.PERCENT_FLOAT);
            //vehcle price G6
            Number G6 = new Number(6, 5, loanRequest.getPrice(), numberFormat);
            businessForm.addCell(G6);

            //insurance number J5
            Label J5 = new Label(9, 4, loanRequest.getInsuranceCoverNo());
            businessForm.addCell(J5);

            //request person name G8
            businessForm.addCell(new Label(6, 7, loanRequest.getRequestPerson().getName()));

            //req person id type J8
            businessForm.addCell(new Label(9, 7, getText("ID_TYPE_".concat(loanRequest.getRequestPerson().getIdType().toString()), request.getLocale())));

            //req person id number D9
            businessForm.addCell(new Label(3, 8, loanRequest.getRequestPerson().getIdNo()));

            // req person phone number D10
            businessForm.addCell(new Label(3, 9, loanRequest.getRequestPerson().getMobileNo()));

            // req person address type J10
            businessForm.addCell(new Label(9, 9, "居住地址"));

            //req person address D11
            businessForm.addCell(new Label(3, 10, loanRequest.getRequestPerson().getAddress().toString()));

            //req person id expire time G9 To-do
            if(loanRequest.getRequestPerson().getDueDate()!=null) {
                businessForm.addCell(new DateTime(6, 8, loanRequest.getRequestPerson().getDueDate(), dateFormat));
            }
            //age C20
            businessForm.addCell(new Number(2, 19, loanRequest.getRequestPerson().getAge()));

            // company nature G20
            businessForm.addCell(new Label(6, 19, getText("COMPANY_NATURE_".concat(loanRequest.getRequestPerson().getCompanyNature().toString()), request.getLocale())));

            // company and job year J20
            businessForm.addCell(new Label(9, 19, loanRequest.getRequestPerson().getWorkCompany() + "/" +loanRequest.getRequestPerson().getCompanyYears()));

            //job C21
            businessForm.addCell(new Label(2, 20, getText("WORK_POSITION_".concat(loanRequest.getRequestPerson().getWorkPosition().toString()), request.getLocale())));

            // credit mobile G21
            businessForm.addCell(new Label(6, 20, loanRequest.getRequestPersonCreditQuestionnaire().getMobileNo()));

            // car price - income  F22
            businessForm.addCell(new Number(5, 21,loanRequest.getPrice()-loanRequest.getRequestPerson().getAnnualIncome()));

            //loan downpay amount c27
            businessForm.addCell(new Number(2, 26, loanRequest.getDownPaymentAmount(), numberFormat));

            //downpay rate g27
            businessForm.addCell(new Number(6, 26, loanRequest.getDownPaymentRate()/100, percentFormat));

            // loan amount c28
            businessForm.addCell(new Number(2, 27, loanRequest.getLoanAmount(), numberFormat));
            // loan rate g28
            businessForm.addCell(new Number(6, 27, loanRequest.getLoanRate()/100, percentFormat));
            //req person credit level c33
            businessForm.addCell(new Label(2, 32, loanRequest.getRequestPersonCreditQuestionnaire().getCreditLevel()));
            //insurance amount c31
            businessForm.addCell(new Number(2, 30, loanRequest.getLoanAmount()*1.1, numberFormat));
            //insurance duty amount g32
            businessForm.addCell(new Number(6, 31, loanRequest.getLoanAmount()*1.1, numberFormat));

            target.write();
            target.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        }

        OutputStream os = response.getOutputStream();
        try {
            response.reset();
            response.setHeader("Content-Disposition", "attachment; filename=".concat(MimeUtility.encodeWord("业务申请单.xls")));
            response.setContentType("application/octet-stream; charset=utf-8");
            os.write(FileUtils.readFileToByteArray(targetFile));
            os.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

    @RequestMapping(value = "/insuranceForm", method = RequestMethod.GET)
    public void exportInsuranceForm(@RequestParam(required = true, value = "reqId") Long reqId, HttpServletRequest request,HttpServletResponse response) throws IOException {
        LoanRequest loanRequest = loanRequestManager.loadWholeLoanRequest(reqId);

        String path =request.getServletContext().getRealPath("")
                + "/excelTemplate/投保单.xls";

        File targetFile = new File(getUploadFolder(loanRequest) + "/投保单.xls");
        try {
            Workbook template = Workbook.getWorkbook(new File(path));
            WritableWorkbook target = Workbook.createWorkbook(targetFile, template);
            WritableSheet businessForm = target.getSheet(0);

            DateFormat customDateFormat = new DateFormat ("yyyy-MM-dd");
            WritableCellFormat dateFormat = new WritableCellFormat (customDateFormat);

            WritableCellFormat numberFormat = new WritableCellFormat(new NumberFormat("#.##"));
            WritableCellFormat percentFormat = new WritableCellFormat( NumberFormats.PERCENT_FLOAT);

            //request person name D6
            businessForm.addCell(new Label(3, 5, loanRequest.getRequestPerson().getName()));
            //req person id number G6
            businessForm.addCell(new Label(6, 5, loanRequest.getRequestPerson().getIdNo()));
            //req person sex K6
            businessForm.addCell(new Label(10, 5, getText("GENDER_".concat(loanRequest.getRequestPerson().getGender().toString()),request.getLocale())));
            // req person phone number D7
            businessForm.addCell(new Label(3, 6, loanRequest.getRequestPerson().getMobileNo()));
            //req person home tel M6
            businessForm.addCell(new Label(12, 5, loanRequest.getRequestPerson().getHomeTelNo()));
            //req person address H9
            businessForm.addCell(new Label(7, 8, loanRequest.getRequestPerson().getAddress().toString()));
            // postal code M9
            businessForm.addCell(new Label(12, 8, loanRequest.getRequestPerson().getAddress().getPostalCode()));
            // company G7
            businessForm.addCell(new Label(6, 6, loanRequest.getRequestPerson().getWorkCompany()));
            //company address E8
            businessForm.addCell(new Label(4, 7, loanRequest.getRequestPerson().getCompanyAddress().toString()));
            //work tel k8
            businessForm.addCell(new Label(10, 7, loanRequest.getRequestPerson().getWorkTelNo()));
            if (loanRequest.isCoLoaned()){
                // couple M8
                businessForm.addCell(new Label(12, 7, loanRequest.getCoPerson().getName()));
                // couple idNo E9
                businessForm.addCell(new Label(4, 8, loanRequest.getCoPerson().getIdNo()));
            }
            // car brand type E10
            businessForm.addCell(new Label(4, 9, loanRequest.getVehicleInfo()));
            // car price L10
            businessForm.addCell(new Number(11, 9, loanRequest.getPrice(), numberFormat));
            // downpay rate E11
            businessForm.addCell(new Number(4, 10, loanRequest.getDownPaymentRate()/100, percentFormat));
            // loan year J13
            businessForm.addCell(new Label(9, 12, loanRequest.getLoanYears()+"年"));
            // dealer E29
            businessForm.addCell(new Label(4, 28, loanRequest.getDealer().getName()));
            //dealer address I29
            businessForm.addCell(new Label(8, 28, loanRequest.getDealer().getMailAddress().toString()));
            businessForm.addCell(new Number(9, 19, loanRequest.getRequestPerson().getCompanyYears()));

            target.write();
            target.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        }

        OutputStream os = response.getOutputStream();
        try {
            response.reset();
            response.setHeader("Content-Disposition", "attachment; filename=".concat(MimeUtility.encodeWord("投保单.xls")));
            response.setContentType("application/octet-stream; charset=utf-8");
            os.write(FileUtils.readFileToByteArray(targetFile));
            os.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

    @RequestMapping(value = "/auditResult", method = RequestMethod.GET)
    public void exportAuditResult(@RequestParam(required = true, value = "reqId") Long reqId, HttpServletRequest request,HttpServletResponse response) throws IOException {
        LoanRequest loanRequest = loanRequestManager.loadWholeLoanRequest(reqId);
        configuration.setDefaultEncoding("utf-8");
        String path =request.getServletContext().getRealPath("")
                + "/excelTemplate";
        configuration.setDirectoryForTemplateLoading(new File(path));
        Template t=null;
        try {
            t = configuration.getTemplate("audit.ftl");
        } catch (IOException e) {
            e.printStackTrace();
        }
        File outFile =  new File(getUploadFolder(loanRequest) + "/audit.doc");
        Writer out = null;
        try {
            out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFile)));
        } catch (FileNotFoundException e1) {
            e1.printStackTrace();
        }
        Map<String,Object> dataMap=new HashMap<String,Object>();
        List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
        Calendar calendar = Calendar.getInstance();
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        for (TalkRecord talkRecord : loanRequest.getTalkRecordList()) {
            calendar.setTime(talkRecord.getTime());
            Map<String,Object> map = new HashMap<String,Object>();
            map.put("riqi", format.format(calendar.getTime()));
            map.put("operator", talkRecord.getOperator());
            map.put("content",talkRecord.getContent());
            if(talkRecord.getNumber()!=null){
                map.put("phone", talkRecord.getNumber());
            } else {
                map.put("phone", "N/A");
            }
            list.add(map);
        }

        dataMap.put("list", list);
        dataMap.put("encode","gb2312");
        dataMap.put("loanrequest", loanRequest.getNumber());
        if (loanRequest.getAuditComment()!=null){
            dataMap.put("internalReview", loanRequest.getAuditComment());
        }else {
            dataMap.put("internalReview", "N/A");
        }
        if( loanRequest.getDealerComment()!=null){
            dataMap.put("businessReview", loanRequest.getDealerComment());
        } else {
            dataMap.put("businessReview", "N/A");
        }
        try {
            t.process(dataMap, out);
        } catch (TemplateException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        OutputStream os = response.getOutputStream();
        try {
            response.reset();
            response.setHeader("Content-Disposition", "attachment; filename=".concat(MimeUtility.encodeWord("审批记录.doc")));
            response.setContentType("application/octet-stream; charset=utf-8");
            os.write(FileUtils.readFileToByteArray(outFile));
            os.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

    @RequestMapping(value = "/creditForm", method = RequestMethod.GET)
    public void exportCreditForm(@RequestParam(required = true, value = "reqId") Long reqId, HttpServletRequest request,HttpServletResponse response) throws IOException {
//        LoanRequest loanRequest = loanRequestManager.loadWholeLoanRequest(reqId);

        String path =request.getServletContext().getRealPath("")
                + "/excelTemplate/资信评分表.xls";

        File targetFile = new File(path);

        OutputStream os = response.getOutputStream();
        try {
            response.reset();
            response.setHeader("Content-Disposition", "attachment; filename=".concat(MimeUtility.encodeWord("资信评分表.xls")));
            response.setContentType("application/octet-stream; charset=utf-8");
            os.write(FileUtils.readFileToByteArray(targetFile));
            os.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }


    @RequestMapping(value = "/bankExportForm", method = RequestMethod.GET)
    public void exportBankExportForm(@RequestParam(required = true, value = "reqId") Long reqId, HttpServletRequest request, HttpServletResponse response) throws IOException {
        LoanRequest loanRequest = loanRequestManager.loadWholeLoanRequest(reqId);

        String path =request.getServletContext().getRealPath("")
                + "/excelTemplate/银行输出表格.xls";

        File targetFile = new File(getUploadFolder(loanRequest) + "/银行输出表格.xls");
        try {
            Workbook template = Workbook.getWorkbook(new File(path));
            WritableWorkbook target = Workbook.createWorkbook(targetFile, template);
            WritableSheet businessForm = target.getSheet(0);

            DateFormat customDateFormat = new DateFormat ("yyyy-MM-dd");
            WritableCellFormat dateFormat = new WritableCellFormat (customDateFormat);

            WritableCellFormat numberFormat = new WritableCellFormat(new NumberFormat("#.##"));

            businessForm.addCell(new Label(2, 1, loanRequest.getRequestPerson().getName()));
            businessForm.addCell(new Label(4, 1, getText("GENDER_".concat(loanRequest.getRequestPerson().getGender().toString()), request.getLocale())));
            businessForm.addCell(new DateTime(2, 2, loanRequest.getRequestPerson().getBirthday(), dateFormat));
            businessForm.addCell(new Number(6, 1, loanRequest.getRequestPerson().getAge()));
            businessForm.addCell(new Label(4, 3, getText("MARITAL_STATUS_".concat(loanRequest.getRequestPerson().getMaritalStatus().toString()), request.getLocale())));
            businessForm.addCell(new Label(4, 2, getText("ID_TYPE_".concat(loanRequest.getRequestPerson().getIdType().toString()), request.getLocale())));
            businessForm.addCell(new Label(6, 2, loanRequest.getRequestPerson().getIdNo()));
            if(loanRequest.getRequestPerson().getDueDate()!=null){
                businessForm.addCell(new DateTime(2, 3, loanRequest.getRequestPerson().getDueDate(), dateFormat));

            }
            businessForm.addCell(new Label(6, 3, loanRequest.getRequestPerson().getMobileNo()));
            businessForm.addCell(new Number(6, 9, loanRequest.getRequestPerson().getAnnualIncome()));
            businessForm.addCell(new Label(2, 4, loanRequest.getRequestPerson().getHomeTelNo()));
            businessForm.addCell(new Label(4, 4, loanRequest.getRequestPerson().getWorkTelNo()));
            businessForm.addCell(new Label(6, 4, loanRequest.getRequestPerson().getHkAddress().toString()));
            businessForm.addCell(new Label(2, 5, loanRequest.getRequestPerson().getAddress().toString()));
            businessForm.addCell(new Number(2, 6, loanRequest.getRequestPerson().getLiveYears()));
            businessForm.addCell(new Label(4, 6, loanRequest.getRequestPerson().getWorkCompany()));
            businessForm.addCell(new Label(6, 5, loanRequest.getRequestPerson().getAddress().getPostalCode()));
            businessForm.addCell(new Label(2, 7, loanRequest.getRequestPerson().getCompanyAddress().toString()));
            businessForm.addCell(new Label(6, 7, loanRequest.getRequestPerson().getCompanyAddress().getPostalCode()));
            businessForm.addCell(new Number(2, 8, loanRequest.getRequestPerson().getCompanyYears()));
            businessForm.addCell(new Label(4, 8, getText("COMPANY_NATURE_".concat(loanRequest.getRequestPerson().getCompanyNature().toString()), request.getLocale())));
            businessForm.addCell(new Label(6, 8, loanRequest.getRequestPerson().getCompanyType()));
            businessForm.addCell(new Label(2, 10, getText("MAIL_TYPE_".concat(loanRequest.getRequestPerson().getMailType().toString()), request.getLocale())));

            if(loanRequest.isCoLoaned()&&loanRequest.isGuaranted()){
                businessForm.addCell(new Label(2, 11, loanRequest.getCoPerson().getName()));
                businessForm.addCell(new Label(5, 11, getText("GENDER_".concat(loanRequest.getCoPerson().getGender().toString()), request.getLocale())));
                businessForm.addCell(new Label(2, 12, getText("loanRequest.coLoaned", request.getLocale())));
                businessForm.addCell(new Label(5, 12, loanRequest.getCoPerson().getMobileNo()));
            }else if(loanRequest.isGuaranted()){
                businessForm.addCell(new Label(2, 11, loanRequest.getDbPerson().getName()));
                businessForm.addCell(new Label(5, 11, getText("GENDER_".concat(loanRequest.getDbPerson().getGender().toString()), request.getLocale())));
                businessForm.addCell(new Label(2, 12, getText("loanRequest.guaranted", request.getLocale())));
                businessForm.addCell(new Label(5, 12, loanRequest.getDbPerson().getMobileNo()));
            }else {
                businessForm.addCell(new Label(2, 11, loanRequest.getUrgentContactName1()));
                businessForm.addCell(new Label(5, 11, getText("GENDER_".concat(loanRequest.getUrgentContactGender1()), request.getLocale())));
                businessForm.addCell(new Label(2, 12, loanRequest.getUrgentContactRelation1()));
                businessForm.addCell(new Label(5, 12, loanRequest.getUrgentContactTelNo1()));
            }
            businessForm.addCell(new Label(2, 13, loanRequest.getVehicleInfo()));
            businessForm.addCell(new Label(2, 14, loanRequest.getLoanProduct().getProductName()));
            businessForm.addCell(new Label(2, 17, loanRequest.getEngineNumber()));
            businessForm.addCell(new Label(5, 17, loanRequest.getFrameNo()));


            businessForm.addCell(new Number(4, 13, loanRequest.getManufacturerPrice(), numberFormat));
            businessForm.addCell(new Number(6, 13, loanRequest.getPrice(), numberFormat));
            businessForm.addCell(new Number(4, 14, loanRequest.getDownPaymentAmount(), numberFormat));
            businessForm.addCell(new Number(2, 16, loanRequest.getDownPaymentRate()/100, percentFormat));
            businessForm.addCell(new Number(2, 15, loanRequest.getMonthPaymentAmount(), numberFormat));
            businessForm.addCell(new Number(4, 16, loanRequest.getLoanRate()/100, percentFormat));
            businessForm.addCell(new Number(6, 14, loanRequest.getLoanAmount(), numberFormat));

            if(loanRequest.getVehiclePurchaseDate()!=null){
                businessForm.addCell(new DateTime(6, 18, loanRequest.getVehiclePurchaseDate(), dateFormat));
            }
            if(loanRequest.getVehicleManufactureDate()!=null){
                businessForm.addCell(new DateTime(4, 15, loanRequest.getVehicleManufactureDate(), dateFormat));
            }

            businessForm.addCell(new Label(6, 19, loanRequest.getInsuranceCoverNo()));
            businessForm.addCell(new Label(6, 15, loanRequest.getLoanYears()+"年"));
            if (loanRequest.getInsureStartDate()!=null){
                businessForm.addCell(new DateTime(2, 19, loanRequest.getInsureStartDate(), dateFormat));
            }
            if (loanRequest.getInsureEndDate()!=null){
                businessForm.addCell(new DateTime(4, 19, loanRequest.getInsureEndDate(), dateFormat));
            }
            businessForm.addCell(new Number(4, 18,  loanRequest.getInsuranceAmount(), numberFormat));

            target.write();
            target.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        }

        OutputStream os = response.getOutputStream();
        try {
            response.reset();
            response.setHeader("Content-Disposition", "attachment; filename=".concat(MimeUtility.encodeWord("信息表.xls")));
            response.setContentType("application/octet-stream; charset=utf-8");
            os.write(FileUtils.readFileToByteArray(targetFile));
            os.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

    private String getUploadFolder(LoanRequest loanRequest){
        String uploadDir = getServletContext().getRealPath("/resources");
        if (uploadDir == null) {
            uploadDir = new File("src/main/webapp/resources").getAbsolutePath();
        }
        uploadDir += File.separator + loanRequest.getNumber() + File.separator + "CAFiles";
        File dirPath = new File(uploadDir);

        if (!dirPath.exists()) {
            dirPath.mkdirs();
        }

        return uploadDir;
    }

}
